﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using MMO_MANAGER.Utils;
// sử dụng 3 file dll: ...SQLite, ...Linq, ...Interop

namespace MMO_MANAGER.DAO
{
    class SqliteHelper
    {
        AccessData dao;
        public SqliteHelper()
        {
            dao = new AccessData();
        }

        // - Table Channel
        public void CreateTablechannel_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [demo_list] (
                          [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                          [id_email] INT, 
                          [id_gpage] INT, 
                          [ten] NVARCHAR, 
                          [id_label] INT, 
                          [id_ga] INT, 
                          [ghichu] NVARCHAR, 
                          [active] INT, 
                          [ngayedit] DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table Email
        public void CreateTableemail_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [email_list] (
                          [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                          [ten] NVARCHAR, 
                          [pwd] NVARCHAR, 
                          [ghichu] NVARCHAR, 
                          [active] INT, 
                          [ngayedit] DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table GA
        public void CreateTablega_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [ga_list] (
                            [id]  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            [tenga]  NVARCHAR,
                            [ghichu]  NVARCHAR,
                            [active]  INT,
                            [ngayedit]  DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table G+
        public void CreateTablegpage_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [gpage_list] (
                          [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                          [ten] NVARCHAR, 
                          [pwd] NVARCHAR, 
                          [trang] NVARCHAR, 
                          [ghichu] NVARCHAR, 
                          [active] INT, 
                          [ngayedit] DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table Keyword
        public void CreateTablekeyword_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [key_word] (
                            [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                            [ten]  NVARCHAR,
                            [tukhoa]  NVARCHAR,
                            [ghichu]  NVARCHAR,
                            [active]  INT,
                            [ngayedit]  DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table Label
        public void CreateTablelabel_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [label_list] (
                            [id]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            [ten]  NVARCHAR,
                            [ghichu]  NVARCHAR,
                            [active]  INT,
                            [ngayedit]  DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table Network
        public void CreateTablenetwork_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [network_list] (
                          [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                          [id_email] INT, 
                          [id_gpage] INT, 
                          [ten] NVARCHAR, 
                          [id_label] INT, 
                          [id_ga] INT, 
                          [ghichu] NVARCHAR, 
                          [active] INT, 
                          [ngayedit] DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        // - Table User
        public void CreateTableuser_list()
        {
            string sql = @"CREATE TABLE IF NOT EXISTS [userlist] (
                            [id]  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            [tendn]  NVARCHAR NOT NULL,
                            [pwd]  NVARCHAR NOT NULL,
                            [tenht]  NVARCHAR,
                            [ngaysinh]  NVARCHAR,
                            [diachi]  NVARCHAR,
                            [sdt]  NVARCHAR,
                            [email]  NVARCHAR,
                            [role]  INTEGER,
                            [active]  INTEGER,
                            [ngayedit]  DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }

        public void InsertAdmin()
        {
            DataTable dt = GetAdmin();
            if (dt.Rows.Count == 0)
            {
                string sql = "Insert Into userlist (tendn, pwd, tenht, ngaysinh, diachi, sdt, email, role, active, ngayedit) " +
                "values (@ten, @pwd, @tenht, @bir, @dc, @dt, @email, @role, @act, @ngayedit)";
                var param = new SQLiteParameter[10];
                param[0] = new SQLiteParameter("@ten", DbType.String);
                param[0].Value = "admin";
                param[1] = new SQLiteParameter("@pwd", DbType.String);
                param[1].Value = "21232f297a57a5a743894a0e4a801fc3";
                param[2] = new SQLiteParameter("@tenht", DbType.String);
                param[2].Value = "Administrator";
                param[3] = new SQLiteParameter("@bir", DbType.String);
                param[3].Value = null;
                param[4] = new SQLiteParameter("@dc", DbType.String);
                param[4].Value = null;
                param[5] = new SQLiteParameter("@dt", DbType.String);
                param[5].Value = null;
                param[6] = new SQLiteParameter("@email", DbType.String);
                param[6].Value = null;
                param[7] = new SQLiteParameter("@role", DbType.Int16);
                param[7].Value = 1;
                param[8] = new SQLiteParameter("@act", DbType.Int16);
                param[8].Value = 1;
                param[9] = new SQLiteParameter("@ngayedit", DbType.DateTime);
                param[9].Value = DateTime.Now;
                dao.ExecuteNoneQuery(sql, param);
                EasyDialog.ShowSuccessfulDialog("Created Database Successfull!\n\nUser: admin, Pass: admin.\n\nPlease change pass when first logon!");
            }
        }

        public DataTable GetAdmin()
        {
            string sql = "select * from userlist WHERE tendn='admin' and active='1'";
            DataTable dt = dao.GetDataSet(sql).Tables[0];
            return dt;
        }
    }
}
